﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using HienThi;

namespace DAO
{
    public class NhapHangDAO
    {
        public List<DonDatHang> LayBangDDH()
        {
            SqlConnection conn = DataProvider.ConnectDB();
            string sqlStr = "select * from DonDatHang";
            SqlCommand cmd = new SqlCommand(sqlStr, conn);
            SqlDataReader dr = cmd.ExecuteReader();
            List<DonDatHang> kq = new List<DonDatHang>();
            while (dr.Read())
            {
                DonDatHang temp = new DonDatHang();
                temp.MaDDH = dr["MaDDH"].ToString();
                temp.NgayDat = DateTime.Parse(dr["NgayDat"].ToString());
                temp.TongTien = float.Parse(dr["TongTien"].ToString());
                temp.NguoiLap = dr["NguoiLap"].ToString();
                temp.PhuongThucTT = int.Parse(dr["PhuongThucTT"].ToString());
                temp.TinhTrang = int.Parse(dr["TinhTrang"].ToString());
                if (temp.TinhTrang == 0)
                    kq.Add(temp);
            }
            conn.Close();
            return kq;

        }
        public List<NhapHangHienThi> LayBangNhapHangHienThi(string maddh)
        {
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand cmdmanh = new SqlCommand("select nh1.MaNH from NhapHang nh1 where nh1.MaDDH = @maddh and LanNhap >= all (select nh2.LanNhap from NhapHang nh2 where nh2.MaDDH = nh1.MaDDH) ", conn);
            SqlParameter pmaddh = new SqlParameter("@maddh", maddh);
            cmdmanh.Parameters.Add(pmaddh);
            SqlDataReader drmanh = cmdmanh.ExecuteReader();
            string manh = "";
            if (drmanh.Read())
                manh = drmanh["MaNH"].ToString();
            drmanh.Close();
            List<NhapHangHienThi> kq = new List<NhapHangHienThi>();
            if (manh == "")
            {
                string sqlStr = "select sp.MaSP, sp.TenSP, ctddh.CV, ctddh.DonGia,ctddh.SoLuong from DonDatHang ddh, ChiTietDonDatHang ctddh, SanPham sp where ddh.MaDDH = ctddh.MaDDH and ctddh.MaSP = sp.ID and ctddh.MaDDH=@maddh";
                SqlCommand cmd = new SqlCommand(sqlStr, conn);
                SqlParameter pma = new SqlParameter("@maddh", maddh);
                cmd.Parameters.Add(pma);
                SqlDataReader dr = cmd.ExecuteReader();
                int i = 1;
                while (dr.Read())
                {
                    NhapHangHienThi temp = new NhapHangHienThi();
                    temp.STT = i;
                    temp.MaSP = dr["MaSP"].ToString();
                    temp.TenSP = dr["TenSP"].ToString();
                    temp.CV = int.Parse(dr["CV"].ToString());
                    temp.DonGia = float.Parse(dr["DonGia"].ToString());
                    temp.SLDat = int.Parse(dr["SoLuong"].ToString());
                    temp.SLNhan = 0;
                    temp.SLConLai = temp.SLDat - temp.SLNhan;
                    if (temp.SLDat != 0)
                    {
                        kq.Add(temp);
                        i++;
                    }
                }
            }
            else
            {
                string sqlStr = "select sp.MaSP, sp.TenSP, ctddh.CV, ctddh.DonGia, ctnh.SLConLai from NhapHang nh,ChiTietNH ctnh,DonDatHang ddh, ChiTietDonDatHang ctddh, SanPham sp where nh.MaNH=@manh and nh.MaDDH = ddh.MaDDH and nh.MaNH = ctnh.MaNH and ddh.MaDDH = ctddh.MaDDH and ctnh.MaSP = sp.MaSP and ctddh.MaSP = sp.ID";
                SqlCommand cmd = new SqlCommand(sqlStr, conn);
                SqlParameter pmanh = new SqlParameter("@manh", manh);
                cmd.Parameters.Add(pmanh);
                SqlDataReader dr = cmd.ExecuteReader();
                int i = 1;
                while (dr.Read())
                {
                    NhapHangHienThi temp = new NhapHangHienThi();
                    temp.STT = i;
                    temp.MaSP = dr["MaSP"].ToString();
                    temp.TenSP = dr["TenSP"].ToString();
                    temp.CV = int.Parse(dr["CV"].ToString());
                    temp.DonGia = float.Parse(dr["DonGia"].ToString());
                    temp.SLDat = int.Parse(dr["SLConLai"].ToString());
                    temp.SLNhan = 0;
                    temp.SLConLai = temp.SLDat - temp.SLNhan;
                    if (temp.SLDat != 0)
                    {
                        kq.Add(temp);
                        i++;
                    }
                }

            }
            conn.Close();
            return kq;
        }
        public List<NhanVien> LayBangNhanVien()
        {
            SqlConnection conn = DataProvider.ConnectDB();
            string sqlStr = "select * from NhanVien";
            SqlCommand cmd = new SqlCommand(sqlStr, conn);
            SqlDataReader dr = cmd.ExecuteReader();
            List<NhanVien> kq = new List<NhanVien>();
            while (dr.Read())
            {
                NhanVien temp = new NhanVien();
                temp.MaNhanVien = dr["MaNhanVien"].ToString();
                temp.TenNhanVien = dr["TenNhanVien"].ToString();
                temp.MatKhau = "";
                temp.TenDN = "";
                kq.Add(temp);
            }
            conn.Close();
            return kq;
        }
        public List<string> LayNgayNhapHang()
        {
            SqlConnection conn = DataProvider.ConnectDB();
            string sqlStr = "select distinct DAY(NgayNhap)as Ngay,MONTH(NgayNhap)as Thang,YEAR(NgayNhap)as Nam, MaDDH from NhapHang";
            SqlCommand cmd = new SqlCommand(sqlStr, conn);
            SqlDataReader dr = cmd.ExecuteReader();
            List<string> kq = new List<string>();
            while (dr.Read())
            {
                string temp;
                temp = dr["Ngay"].ToString() + "/" + dr["Thang"].ToString() + "/" + dr["Nam"].ToString() + "_" + dr["MaDDH"].ToString();
                kq.Add(temp);
            }
            conn.Close();
            return kq;
        }
        public string LayLanNhap(string maddh)
        {
            SqlConnection conn = DataProvider.ConnectDB();
            string sqlStr = "select max(lannhap) from NhapHang where MaDDH = @maddh";
            SqlCommand cmd = new SqlCommand(sqlStr, conn);
            SqlParameter pmaddh = new SqlParameter("@maddh", maddh);
            cmd.Parameters.Add(pmaddh);
            string kq = cmd.ExecuteScalar().ToString();
            conn.Close();
            return kq;
        }
        public void InsertNhapHang(string maddh, string ngaynhap, string manv, int lannhap)
        {
            SqlConnection conn = DataProvider.ConnectDB();
            string sqlStr = "declare @manh varchar(50) = newid() insert into NhapHang values (@manh,@maddh,@ngaynhap,@manv,@lannhap)";
            SqlCommand cmd = new SqlCommand(sqlStr, conn);
            SqlParameter pmaddh = new SqlParameter("@maddh", maddh);
            SqlParameter pngaynhap = new SqlParameter("@ngaynhap", ngaynhap);
            SqlParameter pmanv = new SqlParameter("@manv", manv);
            SqlParameter plannhap = new SqlParameter("@lannhap", lannhap);
            cmd.Parameters.Add(pmaddh);
            cmd.Parameters.Add(pngaynhap);
            cmd.Parameters.Add(pmanv);
            cmd.Parameters.Add(plannhap);
            cmd.ExecuteNonQuery();
            conn.Close();
        }
        public void InsertChiTietNH(string manh, string masp, string slnhan, string slconlai)
        {
            SqlConnection conn = DataProvider.ConnectDB();
            string sqlStr = "declare @mactnh varchar(50)=newid() insert into ChiTietNH values(@mactnh,@manh,@masp,@slnhan,@slconlai)";
            SqlCommand cmd = new SqlCommand(sqlStr, conn);
            SqlParameter pmanh = new SqlParameter("@manh", manh);
            SqlParameter pmasp = new SqlParameter("@masp", masp);
            SqlParameter pslnhan = new SqlParameter("@slnhan", slnhan);
            SqlParameter pslconlai = new SqlParameter("@slconlai", slconlai);
            cmd.Parameters.Add(pmanh);
            cmd.Parameters.Add(pmasp);
            cmd.Parameters.Add(pslnhan);
            cmd.Parameters.Add(pslconlai);
            cmd.ExecuteNonQuery();
            conn.Close();
        }
        public void UpdateDonDatHang(string maddh, string tinhtrang)
        {
            SqlConnection conn = DataProvider.ConnectDB();
            string sqlStr = "update DonDatHang set TinhTrang = @tinhtrang where MaDDH = @maddh";
            SqlCommand cmd = new SqlCommand(sqlStr, conn);
            SqlParameter ptinhtrang = new SqlParameter("@tinhtrang", tinhtrang);
            SqlParameter pmaddh = new SqlParameter("@maddh", maddh);
            cmd.Parameters.Add(ptinhtrang);
            cmd.Parameters.Add(pmaddh);
            cmd.ExecuteNonQuery();
            conn.Close();
        }
        public List<string> LayMaNH_Tim(string[] ngaynhap, string maddh)
        {
            SqlConnection conn = DataProvider.ConnectDB();
            string sqlStr = "select MaNH from NhapHang where DAY(NgayNhap) = @ngay and MONTH(NgayNhap)=@thang and YEAR(NgayNhap) = @nam and MaDDH = @maddh";
            SqlCommand cmd = new SqlCommand(sqlStr, conn);
            SqlParameter pngay = new SqlParameter("@ngay", ngaynhap[0]);
            SqlParameter pthang = new SqlParameter("@thang", ngaynhap[1]);
            SqlParameter pnam = new SqlParameter("@nam", ngaynhap[2]);
            SqlParameter pmaddh = new SqlParameter("@maddh", maddh);
            cmd.Parameters.Add(pngay);
            cmd.Parameters.Add(pthang);
            cmd.Parameters.Add(pnam);
            cmd.Parameters.Add(pmaddh);
            SqlDataReader dr = cmd.ExecuteReader();
            List<string> kq = new List<string>();
            while (dr.Read())
            {
                string temp = dr["MaNH"].ToString();
                kq.Add(temp);
            }
            conn.Close();
            return kq;
        }
        public List<NhapHangHienThi> LayBangNhapHangHienThi_Tim(string manh)
        {
            SqlConnection conn = DataProvider.ConnectDB();
            string sqlStr = "select sp.MaSP, sp.TenSP, ctddh.CV, ctddh.DonGia, (ctnh.SLNhan+ctnh.SLConLai) as 'SoLuong', ctnh.SLNhan,ctnh.SLConLai from NhapHang nh,ChiTietNH ctnh,DonDatHang ddh, ChiTietDonDatHang ctddh, SanPham sp where nh.MaNH=@manh and nh.MaDDH = ddh.MaDDH and nh.MaNH = ctnh.MaNH and ddh.MaDDH = ctddh.MaDDH and ctnh.MaSP=sp.MaSP and sp.ID = ctddh.MaSP";
            SqlCommand cmd = new SqlCommand(sqlStr, conn);
            SqlParameter pmanh = new SqlParameter("@manh", manh);
            cmd.Parameters.Add(pmanh);
            SqlDataReader dr = cmd.ExecuteReader();
            List<NhapHangHienThi> kq = new List<NhapHangHienThi>();
            int dem = 1;
            while (dr.Read())
            {
                NhapHangHienThi temp = new NhapHangHienThi();
                temp.STT = dem;
                temp.MaSP = dr["MaSP"].ToString();
                temp.TenSP = dr["TenSP"].ToString();
                temp.CV = int.Parse(dr["CV"].ToString());
                temp.DonGia = float.Parse(dr["DonGia"].ToString());
                temp.SLDat = int.Parse(dr["SoLuong"].ToString());
                temp.SLNhan = int.Parse(dr["SLNhan"].ToString());
                temp.SLConLai = int.Parse(dr["SLConLai"].ToString());
                kq.Add(temp);
                dem++;
            }
            conn.Close();
            return kq;
        }
        public string LayMaNH(string maddh)
        {
            SqlConnection conn = DataProvider.ConnectDB();
            string sqlStr = "select nh1.MaNH from NhapHang nh1 where nh1.MaDDH = @maddh and LanNhap >= all (select nh2.LanNhap from NhapHang nh2 where nh2.MaDDH = nh1.MaDDH) ";
            SqlCommand cmd = new SqlCommand(sqlStr, conn);
            SqlParameter pmaddh = new SqlParameter("@maddh", maddh);
            cmd.Parameters.Add(pmaddh);
            string kq = "";
            SqlDataReader dr = cmd.ExecuteReader();
            if (dr.Read())
                kq = dr["MaNH"].ToString();
            conn.Close();
            return kq;
        }
        public void UpdateChiTietNH(string manh, string masp, int slnhan, int slconlai)
        {
            SqlConnection conn = DataProvider.ConnectDB();
            string sqlStr = "update ChiTietNH set SLNhan=@slnhan, SLConLai=@slconlai where MaSP=@masp and MaNH=@manh";
            SqlCommand cmd = new SqlCommand(sqlStr, conn);
            SqlParameter pmasp = new SqlParameter("@masp", masp);
            SqlParameter pmanh = new SqlParameter("@manh", manh);
            SqlParameter pslnhan = new SqlParameter("@slnhan", slnhan);
            SqlParameter pslconlai = new SqlParameter("@slconlai", slconlai);
            cmd.Parameters.Add(pslnhan);
            cmd.Parameters.Add(pslconlai);
            cmd.Parameters.Add(pmasp);
            cmd.Parameters.Add(pmanh);
            cmd.ExecuteNonQuery();
            conn.Close();
        }
        public bool KiemTraTinhTrangDDH(string maddh)
        {
            SqlConnection conn = DataProvider.ConnectDB();
            string sqlStr = "select TinhTrang from DonDatHang where MaDDH=@maddh";
            SqlCommand cmd = new SqlCommand(sqlStr, conn);
            SqlParameter pmaddh = new SqlParameter("@maddh", maddh);
            cmd.Parameters.Add(pmaddh);
            string kt = cmd.ExecuteScalar().ToString();
            if (kt == "1")
                return true;
            else
                return false;

        }
        
    }
}
